<?php
class sis_sijaka_master_model extends MY_Model {
	var $model = 'sis_sijaka_master';
	
	function GetJSON($as_method = '')
	{
		$ls_sql	= "	SELECT	A.ID_SIJAKA, A.ID_ANGGOTA, TO_CHAR(A.TGL_PERMOHONAN,'DD/MM/YYYY') TGL_PERMOHONAN, A.JANGKA_WAKTU, A.JML_DANA, A.NOMINAL_SPLIT, A.SUKU_BUNGA, 
							A.JENIS_RO, A.PERIODE_BAYAR_BUNGA, A.SISTEM_BAYAR_BUNGA, A.SISTEM_SETOR_AWAL, A.BIAYA_MATERAI, 
							A.BIAYA_TRANSFER, TO_CHAR(A.TGL_SETOR,'DD/MM/YYYY') TGL_SETOR, TO_CHAR(A.TGL_CLOSE,'DD/MM/YYYY') TGL_CLOSE, 
							A.KD_BANK, A.NAMA_CABANG, A.NO_REKENING, A.PEMILIK_REKENING, 
							A.ST_APPROVE, F_SIS_STATUS_SIJAKA(A.ID_SIJAKA) STATUS_APPROVE, TO_CHAR(A.TGL_APPROVE,'DD/MM/YYYY') TGL_APPROVE, A.APPROVE_USER, 
							A.ST_TRANSFER, TO_CHAR(A.TGL_TRANSFER,'DD/MM/YYYY') TGL_TRANSFER, A.BUKTI_TRANSFER, 
							C.FULL_NAME, D.NAMA_JENIS_ANGGOTA, E.NAMA_BANK
					FROM	SIS_SIJAKA_MASTER A
							LEFT JOIN SIS_ANGGOTA B ON A.ID_ANGGOTA = B.ID_ANGGOTA
							LEFT JOIN SW_PERSON C ON B.PERSON_ID = C.PERSON_ID
							LEFT JOIN SIS_SETUP_JENIS_ANGGOTA D ON B.KD_JENIS_ANGGOTA = D.KD_JENIS_ANGGOTA
							LEFT JOIN KEU_BANK E ON E.KD_BANK = A.KD_BANK
					WHERE	(UPPER(NVL(A.ID_SIJAKA,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(A.ID_ANGGOTA,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(C.FULL_NAME,'')) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(NVL(C.IDENTITY_NUMBER,'')) LIKE '%".strtoupper(post('keyword'))."%') 
							".(post('kd_jenis_anggota') != '' ? " AND B.KD_JENIS_ANGGOTA = '".post('kd_jenis_anggota')."' " : '')."
							".(post('st_approve') != '' ? " AND NVL(A.ST_APPROVE,'0') = '".post('st_approve')."' " : '')."
							"./*($as_method == 'NEW' ? " AND NVL(A.ST_APPROVE,'0') IN ('0') " : '').*/"
							"./*($as_method == 'APPROVAL' ? " AND NVL(A.ST_APPROVE,'0') IN ('0','1') AND NVL(A.ST_TRANSFER,'0') = '0' " : '').*/"
					ORDER BY ".(post('sort')=='' ? "NVL(A.ST_APPROVE,'0') ASC, A.TGL_PERMOHONAN" : post('sort'))." ".(post('order')=='' ? 'desc' : post('order'));
					
		return json_encode( $this->paging($ls_sql) );
	}
	
	function GetDetail($param=array())
	{
		
		$ls_sql	= "	SELECT	A.ID_SIJAKA, A.ID_ANGGOTA, TO_CHAR(A.TGL_PERMOHONAN,'DD/MM/YYYY') TGL_PERMOHONAN, A.JANGKA_WAKTU, A.JML_DANA, A.NOMINAL_SPLIT, A.SUKU_BUNGA, 
							A.JENIS_RO, A.PERIODE_BAYAR_BUNGA, A.SISTEM_BAYAR_BUNGA, A.SISTEM_SETOR_AWAL, A.BIAYA_MATERAI, 
							A.BIAYA_TRANSFER, TO_CHAR(A.TGL_SETOR,'DD/MM/YYYY') TGL_SETOR, TO_CHAR(A.TGL_CLOSE,'DD/MM/YYYY') TGL_CLOSE, 
							A.KD_BANK, A.NAMA_CABANG, A.NO_REKENING, A.PEMILIK_REKENING, 
							A.ST_APPROVE, F_SIS_STATUS_SIJAKA(A.ID_SIJAKA) STATUS_APPROVE, TO_CHAR(A.TGL_APPROVE,'DD/MM/YYYY') TGL_APPROVE, A.APPROVE_USER, 
							A.ST_TRANSFER, TO_CHAR(A.TGL_TRANSFER,'DD/MM/YYYY') TGL_TRANSFER, A.BUKTI_TRANSFER
					FROM	SIS_SIJAKA_MASTER A
							LEFT JOIN SIS_ANGGOTA B ON A.ID_ANGGOTA = B.ID_ANGGOTA
							LEFT JOIN SW_PERSON C ON B.PERSON_ID = C.PERSON_ID
							LEFT JOIN SIS_SETUP_JENIS_ANGGOTA D ON B.KD_JENIS_ANGGOTA = D.KD_JENIS_ANGGOTA
							LEFT JOIN KEU_BANK E ON E.KD_BANK = A.KD_BANK
					WHERE	(UPPER(NVL(A.ID_SIJAKA,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(A.ID_ANGGOTA,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(C.FULL_NAME,'')) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(NVL(C.IDENTITY_NUMBER,'')) LIKE '%".strtoupper(post('keyword'))."%') 
							".(post('kd_jenis_anggota') != '' ? " AND B.KD_JENIS_ANGGOTA = '".post('kd_jenis_anggota')."' " : '')."
							".($as_method == 'NEW' ? " AND NVL(A.ST_APPROVE,'0') IN ('0') " : '')."
							".($as_method == 'APPROVAL' ? " AND NVL(A.ST_APPROVE,'0') IN ('0','1') AND NVL(A.ST_TRANSFER,'0') = '0' " : '')."
					ORDER BY NVL(A.ST_APPROVE,'0') ASC, A.TGL_PERMOHONAN";

		$result = $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			return $result->result_array();
		}else{
			return NULL;
		}
	}
	
	function Add($arr_data){
		$this->db->insert('sis_sijaka_master', $arr_data);
	}
	
	function Update($arr_data, $arr_rule){
		$this->db->update('sis_sijaka_master', $arr_data, $arr_rule);
	}
	
	function GetSingle($as_idpinjaman){
		$result = $this->db->get_where('sis_sijaka_master', array('ID_SIJAKA' => $as_idpinjaman));
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}
	
	function GetSingleView($as_idpinjaman)
	{
		$result = $this->db->query("SELECT	A.ID_SIJAKA, A.ID_ANGGOTA, TO_CHAR(A.TGL_PERMOHONAN,'DD/MM/YYYY') TGL_PERMOHONAN, A.JANGKA_WAKTU, A.JML_DANA, A.NOMINAL_SPLIT, A.SUKU_BUNGA, 
											A.JENIS_RO, A.PERIODE_BAYAR_BUNGA, A.SISTEM_BAYAR_BUNGA, A.SISTEM_SETOR_AWAL, A.BIAYA_MATERAI, 
											A.BIAYA_TRANSFER, TO_CHAR(A.TGL_SETOR,'DD/MM/YYYY') TGL_SETOR, TO_CHAR(A.TGL_CLOSE,'DD/MM/YYYY') TGL_CLOSE, 
											A.KD_BANK, A.NAMA_CABANG, A.NO_REKENING, A.PEMILIK_REKENING, 
											A.ST_APPROVE, F_SIS_STATUS_SIJAKA(A.ID_SIJAKA) STATUS_APPROVE, TO_CHAR(A.TGL_APPROVE,'DD/MM/YYYY') TGL_APPROVE, A.APPROVE_USER, 
											A.ST_TRANSFER, TO_CHAR(A.TGL_TRANSFER,'DD/MM/YYYY') TGL_TRANSFER, A.BUKTI_TRANSFER,
											C.FULL_NAME, D.NAMA_JENIS_ANGGOTA, E.NAMA_BANK
									FROM	SIS_SIJAKA_MASTER A
											LEFT JOIN SIS_ANGGOTA B ON A.ID_ANGGOTA = B.ID_ANGGOTA
											LEFT JOIN SW_PERSON C ON B.PERSON_ID = C.PERSON_ID
											LEFT JOIN SIS_SETUP_JENIS_ANGGOTA D ON B.KD_JENIS_ANGGOTA = D.KD_JENIS_ANGGOTA
											LEFT JOIN KEU_BANK E ON E.KD_BANK = A.KD_BANK
									WHERE	A.ID_SIJAKA = '".$as_idpinjaman."'");
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}

	function DeleteSingle($arr_rule)
	{
		try {
			if(	intval($this->get_data("SELECT COUNT(1) FROM SIS_SIJAKA_MASTER WHERE ID_SIJAKA = '".$arr_rule['id_sijaka']."' AND NVL(ST_APPROVE,'0') <> '0'"))>0)
			{
				return $this->error_delete.' [id: '.$arr_rule['id_sijaka']."]<br />";
			}else{
				$this->db->delete('sis_sijaka_historis_bunga', array('ID_SIJAKA' => $arr_rule['id_sijaka']));
				$this->db->delete('sis_sijaka_historis_rollover', array('ID_SIJAKA' => $arr_rule['id_sijaka']));
				$this->db->delete('sis_sijaka_sertifikat', array('ID_SIJAKA' => $arr_rule['id_sijaka']));
				$this->db->delete('sis_sijaka_master', array('ID_SIJAKA' => $arr_rule['id_sijaka']));
				return '';
			}
		} catch(Exception $e) {
			return $e->getMessage();
		}
	}

	function GetSingleCertView($as_idcert)
	{
		$result = $this->db->query("SELECT 	G.NO_SERTIFIKAT, A.ID_SIJAKA,A.JANGKA_WAKTU, 
							TO_CHAR (G.TGL_AWAL, 'DD/MM/YYYY') TGL_AWAL, 
							TO_CHAR (G.TGL_AKHIR, 'DD/MM/YYYY') TGL_AKHIR,
							(CASE
							    WHEN NVL (A.JENIS_RO, '1') = '1' THEN 'Nominal Saja'
							    ELSE 'Nominal dan Margin'
							END)
							JENIS_RO,
							(CASE
							    WHEN NVL (A.PERIODE_BAYAR_BUNGA, '1') = '1' THEN 'Setiap Bulan'
							    ELSE 'Saat Jatuh Tempo'
							END)
							PEMBAYARAN_BUNGA,G.JML_DANA,
							(SELECT MIN(X.JML_BUNGA) FROM SIS_SIJAKA_HISTORIS_BUNGA X WHERE G.ID_SIJAKA = X.ID_SIJAKA AND G.NO_SERTIFIKAT = X.NO_SERTIFIKAT AND G.PERIODE_RO = X.PERIODE_RO  ) JML_BUNGA, 
							D.FULL_NAME, D.IDENTITY_NUMBER, E.ADDRESS,E.RT,E.RW,E.VILLAGE,E.SUBDISTRICT,F.CITY_NAME
					FROM	SIS_SIJAKA_MASTER A
						    LEFT JOIN SIS_ANGGOTA C ON A.ID_ANGGOTA = C.ID_ANGGOTA
						    LEFT JOIN SW_PERSON D ON C.PERSON_ID = D.PERSON_ID
						    LEFT JOIN SW_PERSON_ADDRESS E ON C.PERSON_ID = E.PERSON_ID
						    LEFT JOIN SW_CITY F ON E.CITY_CODE = F.CITY_CODE
						    LEFT JOIN SIS_SIJAKA_HISTORIS_ROLLOVER G ON A.ID_SIJAKA = G.ID_SIJAKA
					WHERE	G.NO_SERTIFIKAT = '".$as_idcert."'");
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}

	function getDetailSijaka($as_idsijaka)
	{
		$result = $this->db->query("
					SELECT 
							ID_SIJAKA, NO_SERTIFIKAT
					FROM	SIS_SIJAKA_SERTIFIKAT
					WHERE	ID_SIJAKA = '".$as_idsijaka."'");
		if($result->num_rows() > 0){
			return $result->result_array();
		}else{
			return array();
		}
	}
}
?>